
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spShippingStatusReportGetListByParam]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [spShippingStatusReportGetListByParam]
GO
	
CREATE PROCEDURE spShippingStatusReportGetListByParam
	@OfficeID int,
	@SupplierCode int,
	@OrderNumber int,
	@OrderDate datetime,
	@incompleted bit
AS

SELECT
	[ShippingStatusReport].[ID],
	[REQ],
	[OrderDate],
	[ContainerSize],
	[LP],
	[PONo_Office],
	[PONo_Type],
	[PONo_Supplier],
	[PONo_Number],
	[IsPrimary],
	[PONo_Consolidation],
	[AccNo],
	[CustomerOrLocation],
	[FinalDestination],
	[Description],
	[FactoryETD],
	[ShippingDate],
	[ShippingLine],
	[Vessel],
	[ContainerNo],
	[ETA],
	[Week],
	[ArrivalPort],
	[PI],
	[Payment],
	[DocReceived],
	[CompletedDate],
	[BulkType],
	[ModifiedDate],
	[BulkOrderNo],
	[PayAuth],
	[ContainerDeliveried],
	[POInOpera],
	[InvValue],
	[PayDate],
	[Remarks],
	[SInvoiced],
	[SuppOrderNo],
	[OnHold],
	Offices.[OfficeID],
	Offices.OfficeName as OfficeName, 
	[CustPONo],
	[BulkOrderOfficeID],
	[PDepValue],
	[SalesOrder],
	[SalesInvoiceValue],
	[SalesInvoiceDate],
	[SalesPaymentDue],
	[TargetETD],
	[IsArchived],
	[ResponsibleID],
	[FactoryETDEnteredDate]
FROM
	[ShippingStatusReport] LEFT JOIN Offices ON [ShippingStatusReport].OfficeID = Offices.OfficeID
WHERE (@OfficeID = -1 OR Offices.OfficeID=@OfficeID)
AND (@SupplierCode = -1 OR [PONo_Supplier] = @SupplierCode)
AND (@OrderNumber = -1 OR [PONo_Number]= @OrderNumber)
AND (@OrderDate = '1/1/1900' OR OrderDate = @OrderDate)
AND ((@incompleted = 1 AND [CompletedDate] IS NULL) OR (@incompleted = 0 AND [CompletedDate] IS NOT NULL))
ORDER BY [OrderDate] DESC

GO

	
 